
package cn.hpclub.server.service.recommend;

import cn.hpclub.server.model.recommend.Recommend;
import cn.hpclub.server.util.DateKit;
import cn.hpclub.server.util.ObjectUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @FileName: RecommendService.java
 * @Author zhl
 * @Description:推荐有奖Service
 * @Date 2016年8月5日 下午2:38:57
 * @CopyRight ZTE Corporation
 */
public class RecommendService {

    private static final Logger logger = LoggerFactory.getLogger(RecommendService.class);
    public static RecommendService service = new RecommendService();


    /**
     * @param user_id
     * @return
     * @description: 获取当前用户的推荐相关信息
     */
    public Map<String, Object> getRecommendInfo(int user_id) {
        Map<String,Object> map = new HashMap<>();
        boolean newUser = true;
        String recommender_phone = "";
        Record record = getRecommendListData(user_id);
        if (ObjectUtil.isNotEmpty(record)) {
            recommender_phone = record.getStr("recommender_phone");
        }else{
            newUser = getServiceCountData(user_id) > 0 ? false : true;
        }
        map.put("newUser",newUser);
        map.put("recommender_phone",recommender_phone);
        List<Map<String,Object>> list = getRecommendBySlave(user_id);
        map.put("list",list);
        return map;
    }


    /**
     * 通过电话号码查找member_org数据
     * @param phone
     * @return
     */
    public Record getMemberOrgByPhone(String phone){
        String sql = "select * from member_org where user_name=?";
        Record record = Db.findFirst(sql, phone);
        return record;
    }

    /**
     * 通过user_id查找recommend数据
      * @param user_id
     * @return
     */
    private Record getRecommendListData(int user_id) {
        String sql = "select *  from recommend where user_id=? ";
        Record record = Db.findFirst(sql, user_id);
        return record;
    }

    /**
     * 通过user_id查找当前用户的业务办理量是否大于0
     * @param user_id
     * @return
     */
    private long getServiceCountData(int user_id){
        long service_number = 0;
        Record record = null;
        String sql = "select 1 as service_number from sales_promotion where userid=? limit 1 ";
        record = Db.findFirst(sql, user_id);
        service_number = ObjectUtil.isNotEmpty(record)?Long.parseLong(record.get("service_number").toString()):0;
        if(service_number > 0){
            return service_number;
        }
        sql = "select 1 as service_number from service_reservation where user_id=? limit 1 ";
        record = Db.findFirst(sql, user_id);
        service_number = ObjectUtil.isNotEmpty(record)?Long.parseLong(record.get("service_number").toString()):0;
        if(service_number > 0){
            return service_number;
        }
        return service_number;
    }
    /**
     * 通过user_id查询用户推荐的人
     * @param user_id
     * @return
     */
    private List<Map<String,Object>> getRecommendBySlave(int user_id){
        String sql = " select r.* from recommend r join member_org m on r.recommender_phone=m.user_name " +
                " where m.id=? ";
        List<Record> list = Db.find(sql, user_id);
        List<Map<String,Object>> list2 = new ArrayList<>();
        for (Record record : list) {
            Map<String,Object> map = new HashMap<>();
            map.put("name", record.getStr("name"));
            map.put("phone", record.getStr("phone"));
            Date first_service_time = record.getDate("first_service_time");
            if(ObjectUtil.isEmpty(first_service_time)){
                int record_user_id = record.getInt("user_id");
                Record firstRecord = getFirstServiceInfo(record_user_id);
                if(ObjectUtil.isNotEmpty(firstRecord) && ObjectUtil.isNotEmpty(firstRecord.getDate("time"))){
                    first_service_time = firstRecord.getDate("time");
                    record.set("first_service_time",first_service_time);
                    record.set("first_order_id",firstRecord.get("order_id"));
                }
            }
            String first_service_time_str = ObjectUtil.isNotEmpty(first_service_time)?DateKit.toStr(first_service_time,"yyyy-MM-dd HH:mm:ss"):"";
            if(ObjectUtil.isEmpty(first_service_time_str)){
                first_service_time_str = "暂未办理业务";
            }
            map.put("first_service_time", first_service_time_str);
            list2.add(map);
        }
        Db.batch("update recommend set first_service_time = ? where id = ?",
                "first_service_time,id",list,list.size());

        return list2;
    }
    /**
     * 获取用户第一笔办理的业务信息
     * @param user_id
     * @return
     */
    public Record getFirstServiceInfo(int user_id){
        String sql = "select * from sales_promotion where userid=?  and return_code=0 order by time ASC limit 0,1";
        Record record_sp =  Db.findFirst(sql, user_id);
        sql = "select * from service_reservation where user_id=?  and boss_no is not null  order by create_date ASC limit 0,1";
        Record record_sr =  Db.findFirst(sql, user_id);
        if(ObjectUtil.isNotEmpty(record_sr)){
            record_sr.set("order_id",record_sp.get("boss_no"));
        }
        if(ObjectUtil.isNotEmpty(record_sp) && ObjectUtil.isEmpty(record_sr)){
            return record_sp;
        }else if(ObjectUtil.isNotEmpty(record_sr) && ObjectUtil.isEmpty(record_sp)){
            record_sr.set("time", record_sr.get("create_date"));
            return record_sr;
        }else if(ObjectUtil.isNotEmpty(record_sp) && ObjectUtil.isNotEmpty(record_sr)){
            record_sr.set("time", record_sr.get("create_date"));
            Date time_sp = record_sp.getDate("time");
            Date time_sr = record_sr.getDate("create_date");
            return (time_sp.getTime() < time_sr.getTime() )? record_sp : record_sr;
        }else{
            return null;
        }
    }









    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " from recommend r left join member_org m on r.recommender_phone=m.user_name  " +
                " where 1=1  ";

        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForCount(item);
        }
        sqlExceptSelect += " order by r.first_service_time desc ";
        return sqlExceptSelect;
    }
    private String buildQueryForCount(Map.Entry<String, String> item){
        if("start_time".equalsIgnoreCase(item.getKey())){
            String start_time = "'" + item.getValue().replaceAll(" ", "") + " 00:00:00'";
            return " and r.first_service_time >=" + start_time + " ";
        }
        if("end_time".equalsIgnoreCase(item.getKey())){
            String end_time = "'" + item.getValue().replaceAll(" ", "") + " 23:59:59'";
            return " and r.first_service_time <=" + end_time + " ";
        }
        /*if("city_id".equalsIgnoreCase(item.getKey())){
            String city_id = "" + item.getValue().replaceAll(" ", "") + "";
            return " and city_id =" + city_id + " ";
        }*/
        return " and r." + item.getKey() + " like '%" + item.getValue() + "%'";
    }




    /**
     * @describe
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = " SELECT r.id,r.user_id,r.create_time,r.name,r.phone,r.first_service_time,m.name as recommender_name,r.user_award,r.recommender_award FROM " +
                " recommend r left join member_org m on r.recommender_phone=m.user_name WHERE 1=1 ";
        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sql += buildQueryForCount(item);
        }
        sql += " ORDER BY r.first_service_time DESC ";
        List<Record> seq = Db.find(sql);

        String[] excelHeaders = new String[]{"姓名","手机号","推荐人","首笔业务办理时间","用户是否已赠费","推荐人是否已赠费"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"name","phone","recommender_name","first_service_time","user_award","recommender_award"};

        List<Record> recordList = new ArrayList<>();
        for (int i = 0; i < seq.size(); i++) {
            Record record = seq.get(i);
            record.set("user_award",record.getInt("user_award")==1 ? "是" : "否");
            record.set("recommender_award", record.getInt("recommender_award")==1 ? "是" : "否");
            Date first_service_time = record.getDate("first_service_time");
            if(ObjectUtil.isEmpty(first_service_time)){
                int record_user_id = record.getInt(Recommend.USER_ID);
                Record firstRecord = new RecommendService().service.getFirstServiceInfo(record_user_id);
                if(ObjectUtil.isNotEmpty(firstRecord)){
                    first_service_time = firstRecord.getDate("time");
                }
            }
            if(ObjectUtil.isNotEmpty(first_service_time)){
                record.set("first_service_time", first_service_time);
                recordList.add(record);
            }else{
                record.set("first_service_time", "暂未办理业务");
            }

        }
        Db.batch("update recommend set first_service_time = ? where id = ?","first_service_time,id",recordList,recordList.size());
        try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:mm:ss",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("RecommendService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("RecommendService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }


}
